package com.eascs.admin.o2o.detail.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseOaDao;
import com.eascs.admin.o2o.detail.entity.OaLoanDetailEntity;
import com.eascs.admin.o2o.detail.entity.OaUnit;
import com.eascs.admin.o2o.loan.entity.OaBillCodeEntity;

/**
 * 
 * 查询旧系统DAO
 *
 */
@Repository
public class OaDetailDao extends BaseOaDao {
	/**
	 * 查询基本信息
	 * @param billCode
	 * @return
	 */
	public OaLoanDetailEntity queryBillCode(String billCode){
		String sql="SELECT T.LOANAPPLYNO,T.BILLCODECREDIT,T.BILLCODEINFORM,T.BILLCODEOTF,T.BILLCODEMANAGEOTF,T.CREDITRANGE,T.BILLCODE,T.LOANAMOUNT,T.CUSTOMERCODE,F.BANKREFUNDCODE,F.LOADDATE,T.REPAYMENTSOURCE,T.CREATEDATE,T.GUARANTEEWAY,T.LOANTERM,T.REPAYMENTWAY,"
				+ "T.CUSTOMERNAME,T.BANKLOANAMOUNT,T.LOANRATE,T.EXISTDEPOSIT ,T.UNDEPOSIT,T.MANAGEFEE,T.MANAGEFEERATE,T.DEPOSITRATE,"
				+ "T.BACKPAYDATE,T.BACKPAYREMARK,T.BANK_STATUS,T.BANK_APPLYNO,T.BANKTYPE,T.EXISTMANAGEFEE,T.CREDITLINE,T.EXISTRISKDEPOSIT,"
				+ "T.UNRISKDEPOSIT,T.OPERATORNAME,T.LOAN_TYPE,T.PAYDATE,T.CUSTOMER_TYPE,L.LOANERNAME,L.LOANERNAME,L.AGE,L.SEX,L.PROVINCE,"
				+ "L.CITY,L.IDCARDTYPE,L.IDCARD,L.IDAUTHORITY,L.IDEXPIREDATE,L.MARITALSTATUS,L.DIPLOMA,L.ADDRESSPROVINCE,L.ADDRESSCITY,L.ADDRESSCOUNTY"
				+ ",L.NOWADDRESS,L.MOBILE,L.TELEPHONE,L.COUPLENAME,L.COUPLEAGE,L.COUPLEOCCUPATION,L.COUPLEPROVINCE,L.COUPLECITY,L.COUPLEIDCARDTYPE,L.COUPLEIDCARD"
				+ ",L.MARRCHILDREN,T.RISKDEPOSITRATE,L.COUPLEDIPLOMA,L.COUPLENOWADDRESS,L.COUPLEMOBILE,L.COUPLETELEPHONE"
				+ " FROM EADB.O2O_LOANAPPLY T LEFT  JOIN EADB.O2O_LOANER L ON T.LOANAPPLYNO=L.LOANAPPLYNO LEFT JOIN EADB.O2O_REFUND F ON T.LOANAPPLYNO=F.LOANAPPLYNO"
				+ "  WHERE T.ISLOCK='N' AND T.BILLCODE=?";
		return this.findEntity(sql, OaLoanDetailEntity.class, billCode);
	}
	/**
	 * 身份证查询老系统贷款单
	 * @param idCard
	 * @return
	 */
	public List<OaBillCodeEntity> queryBKBillCode(String idCard){
		String sql="SELECT T.BILLCODE FROM EADB.O2O_LOANAPPLY T,EADB.O2O_LOANER L WHERE T.LOANAPPLYNO=L.LOANAPPLYNO AND T.ISLOCK='N' AND T.LOAN_TYPE='BK' AND L.IDCARD=? ORDER BY T.CREATEDATE DESC";
		return this.findEntitys(sql, OaBillCodeEntity.class,idCard);
	}
	/**
	 * 回购担保方ID
	 * @param unitNo
	 * @return
	 */
	public OaUnit findUnit(String unitNo){
		String sql="SELECT UNITNO,UNITCODE,NAMECN FROM EADB.HR_UNITS  WHERE ISVIRTUAL='Y' AND ISDEL='N' AND UNITNO=?";
		return this.findEntity(sql, OaUnit.class,unitNo);
	}
	/**
	 * 联想回购担保方
	 * @param unitNo
	 * @return
	 */
	public List<Object[]> findOAUnit(String name){
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT * FROM (SELECT UNITNO,CONCAT(CONCAT(UNITCODE,'-'),NAMECNSHORT) AS NAMECN ");		
		sql.append(" FROM EADB.HR_UNITS  WHERE ISVIRTUAL='Y' AND ISDEL='N' ");		
		sql.append(" ) WHERE NAMECN LIKE ? ");
		sql.append(" AND ROWNUM<=10 ");
		return this.findObjectsList(sql.toString(), "%"+name.toUpperCase()+"%");
	}
}

